﻿using System;
using System.Linq;
using System.Data.Linq;
using UniMana.Data;
using System.Data;
using UniMana.Utils.Data;
namespace UniMana.Data
{
    public static class Catalogue_SchoolYearData
    {
        /// <summary>
        /// Lấy năm học với khoa, bậc đào tạo, ngành, loại hình đào tạo, khóa học
        /// </summary>
        /// <param name="nSchoolYear"></param>
        /// <param name="nSubjectOpen"></param>
        /// <param name="m_iLevel"></param>
        /// <param name="m_iMajorKey"></param>
        /// <param name="m_iTrainingTypeKey"></param>
        /// <param name="m_iCourse"></param>
        /// <returns></returns>
        public static IQueryable GetSchoolYearByLevelMajorTrainingTypeCourse(
            this Table<Catalogue_SchoolYear> nSchoolYear, 
            Table<Training_Subject_Open> nSubjectOpen, 
            int m_iLevelKey, int m_iMajorKey, 
            int m_iTrainingTypeKey, 
            int m_iCourseKey)
        {
            var schoolyear = (from t in nSubjectOpen
                              join t0 in nSchoolYear on t.Catalogue_SchoolYearKey equals t0.Catalogue_SchoolYearKey
                              where
                                t.Catalogue_LevelKey == m_iLevelKey &&
                                t.Catalogue_MajorKey == m_iMajorKey &&
                                t.Catalogue_TrainingTypeKey == m_iTrainingTypeKey &&
                                t.Catalogue_CourseKey == m_iCourseKey
                              select new
                              {
                                  t.Catalogue_SchoolYearKey,
                                  t0.Name
                              }).Distinct();
            return schoolyear.Distinct();
        }
        /// <summary>
        /// Lấy năm học theo khóa tên khóa học
        /// </summary>
        /// <param name="nSchoolYear">Bảng Catalogue_SchoolYear</param>
        /// <param name="_strCouser">Tên khóa học</param>
        /// <param name="_iQuanity">Số lượng record muốn lấy</param>
        /// <returns>Bảng năm học từ khóa đó trở đi</returns>
        public static DataTable GetSchoolYearByCourser(
                    this Table<Catalogue_SchoolYear> nSchoolYear,
                    string _strCouser,
                    int _iQuanity = 6)
        {
            string[] split = _strCouser.Split('-');
            if (split.Length  == 2)
            {
                string _strSchoolYear = split[1].Replace(" ","");
                DataTable dt = nSchoolYear.ToDataTable();
                if (dt.Columns.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string[] schoolYearName = dt.Rows[i]["Name"].ToString().Split('-');
                        if (schoolYearName.Length == 2)
                        {
                            if (schoolYearName[0].IndexOf(_strSchoolYear) > -1)
                            {
                                break;
                            }
                            else
                            {
                                dt.Rows.RemoveAt(i);
                                i--;
                            }
                        }
                    }
                    while (dt.Rows.Count - _iQuanity > 0)
                    {
                        dt.Rows.RemoveAt(_iQuanity);
                    }
                    return dt;
                }
                return null;
            }
            return null;
        }
        /// <summary>
        /// Lấy năm học trong chương trình đào tạo
        /// </summary>
        /// <param name="nSchoolYear"></param>
        /// <param name="nSubjectOpen"></param>
        /// <returns></returns>
        public static IQueryable GetSchoolYearInTrainingProgram(
            this Table<Catalogue_SchoolYear> nSchoolYear, 
            Table<Training_Subject_Open> nSubjectOpen)
        {
            var schoolyear = (from t in nSubjectOpen
                              join t0 in nSchoolYear on t.Catalogue_SchoolYearKey equals t0.Catalogue_SchoolYearKey
                              select new
                              {
                                  t.Catalogue_SchoolYearKey,
                                  t0.Name
                              }).Distinct();
            return schoolyear.Distinct();
        }
        /// <summary>
        /// Lấy năm học với khoa, ngành, loại hình, khóa học
        /// </summary>
        /// <param name="nSchoolYear"></param>
        /// <param name="nSubjectOpen"></param>
        /// <param name="m_iLevel"></param>
        /// <param name="m_iMajor"></param>
        /// <param name="m_iTrainingType"></param>
        /// <param name="m_iCourse"></param>
        /// <returns></returns>
        public static IQueryable GetSchooYearByLevelMajorTrainingTypeCourse(
            this Table<Catalogue_SchoolYear> nSchoolYear, 
            Table<Training_Subject_Open> nSubjectOpen,
            int m_iLevel, int m_iMajor, 
            int m_iTrainingType, 
            int m_iCourse)
        {
            var schoolyear = (from t in nSubjectOpen
                              join t0 in nSchoolYear on t.Catalogue_SchoolYearKey equals t0.Catalogue_SchoolYearKey 
                              where
                                t.Catalogue_TrainingTypeKey == m_iTrainingType &&
                                t.Catalogue_LevelKey == m_iLevel &&
                                t.Catalogue_MajorKey == m_iMajor &&
                                t.Catalogue_CourseKey == m_iCourse
                              select new
                              {
                                  t.Catalogue_SchoolYearKey,
                                  t0.Name
                              }).Distinct();
            return schoolyear.Distinct();
        }
        /// <summary>
        /// Lấy Danh Sách Năm Học Sau Năm Hiện Tại
        /// </summary>
        /// <param name="nSchoolYear"></param>
        /// <returns></returns>
        public static IQueryable GetSchoolYearAfterNow(
            this Table<Catalogue_SchoolYear> nSchoolYear, int year = 6)
        {
            DateTime dtime = UniMana.Data.SysDateTime.Now;
            DBMain m_Catalogue = new DBMain();
             var Query = from sy in nSchoolYear
                             select sy;
            if (Convert.ToInt32(dtime.Month.ToString()) > 6)
            {
                Query = Query.Where(sy => Convert.ToInt32(sy.Name.Substring(0, sy.Name.IndexOf("-"))) >= 
                                          Convert.ToInt32(dtime.Year.ToString()));
            }
            else
            {
                Query = Query.Where(sy => Convert.ToInt32(sy.Name.Substring(0, sy.Name.IndexOf("-"))) >=
                                          (Convert.ToInt32(dtime.Year.ToString()) - 1));
            }
            return Query.Take(year);
        }
        /// <summary>
        /// Lấy Danh Sách Năm Học Gần Với Năm Hiện Tại
        /// </summary>
        /// <param name="nSchoolYear"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public static IQueryable GetSchoolYearRecentNow(
            this Table<Catalogue_SchoolYear> nSchoolYear, int year = 15)
        {
            DateTime dtime = UniMana.Data.SysDateTime.Now;
            DBMain m_Catalogue = new DBMain();
            var Query = (from sy in nSchoolYear
                         where
                           Convert.ToInt32(sy.Name.Substring(0, sy.Name.IndexOf("-"))) >= 
                           (Convert.ToInt32(dtime.Year.ToString()) - (year/2))
                         select sy).Take(year);
            return Query;
        }
    }
}